#!/usr/bin/env bash
#抽取DWD层行程异常数据到DWS层dws_vehicle_driving_abnormal_statistics表

CUR_DIR=$(cd "$(dirname "${BASH_SOURCE-$0}")"; pwd)
UP_DIR=$(dirname $(dirname $(dirname "$PWD")))
echo ${UP_DIR}
source ${UP_DIR}/system_env.sh


START_TIME=$(date +%s)
hive_db=${hive_db}
hive_db_source=${hive_db_source}

if [ -z "${hive_db}" ];then
  hive_db=dataplatform_trip
else
  hive_db=${hive_db}
fi

if [ -z "${hive_db_source}" ];then
  hive_db_source=dongfeng
else
  hive_db_source=${hive_db_source}
fi


if [ -z "$1" ];then
  part_time=$(date -d "1 days ago" +%Y%m%d)
else
  part_time=$1
fi

#定义参数
START_TIME=`date +%s`
#hive_db=$1
part_time=$1

#生成hql语句
sql_suffix=$(cat <<EOF
insert overwrite table ${hive_db}.dws_vehicle_driving_abnormal_statistics_pdi partition (day=${part_time})
select tid,trip_id,route_start_time,route_end_time,1,trip_mileage/1000,trip_fuel/1000,fuel_100km,-1,-1,-1,-1,-1,-1,-1,-1,-1,'default'
        ,case when trip_duration >= 300 and trip_mileage >= 1000 and trip_fuel > 0
              then 0 else 1 end as export_type
  from ${hive_db}.trip_statistics where fuel_100km < 10 and day = ${part_time}
union all
select tid,trip_id,route_start_time,route_end_time,1,trip_mileage/1000,trip_fuel/1000,fuel_100km,-1,-1,-1,-1,-1,-1,-1,-1,-1,'default'
        ,case when trip_duration >= 300 and trip_mileage >= 1000 and trip_fuel > 0
              then 0 else 1 end as export_type
  from ${hive_db}.trip_statistics where fuel_100km > 90 and day = ${part_time}
union all
select tid,trip_id,-1,-1,2,-1,-1,-1,-1,-1,-1,-1,-1,-1,route_end_mileage/1000,route_end_fuel/1000,100*cast(route_end_fuel/route_end_mileage as decimal(10,2))
 as vehicle_agg_fuel_100km,'default'
 ,0 as export_type
  from (select tid,trip_id,route_end_mileage,route_end_fuel
		,row_number() over (partition by tid order by trip_id desc) rn
		from ${hive_db}.trip_statistics
	   where route_end_mileage > 100000
		 and day = ${part_time}
		 and cross_day_flag = 0
		) as src
  where rn = 1
  and (100*cast(route_end_fuel/route_end_mileage as decimal(10,2)) < 10
	   or
	   100*cast(route_end_fuel/route_end_mileage as decimal(10,2)) > 90)
union all
select tid,trip_id,route_start_time,route_end_time,4,trip_mileage/1000,-1,-1,brake_mileage/1000,cast(brake_mileage/trip_mileage as decimal(10,2)),-1,-1,-1,-1,-1,-1,-1,'default'
,case when trip_duration >= 300 and fuel_100km >0 and fuel_100km <80 and trip_fuel > 0
      then 0 else 1 end as export_type
  from ${hive_db}.trip_statistics where brake_mileage > trip_mileage*0.6 and trip_mileage >= 50000 and day = ${part_time}
union all
select tid,trip_id,route_start_time,route_end_time,4,trip_mileage/1000,-1,-1,brake_mileage/1000,cast(brake_mileage/trip_mileage as decimal(10,2)),-1,-1,-1,-1,-1,-1,-1,'default'
,case when trip_duration >= 300 and fuel_100km >0 and fuel_100km <80 and trip_fuel > 0
      then 0 else 1 end as export_type
  from ${hive_db}.trip_statistics where brake_mileage <= 0 and trip_mileage >= 50000 and day = ${part_time}
union all
select tid,-1,-1,-1,0,-1,-1,-1,-1,-1,trip_mileage/1000,-1,-1,trip_fuel/1000,-1,-1 ,-1,'default',0
   from ${hive_db}.vehicle_basic_data_info_day where trip_mileage >0 and trip_fuel <= 0 and day = ${part_time}
union all
select tid,-1,-1,-1,0,-1,-1,-1,-1,-1,trip_mileage/1000,-1,-1,trip_fuel/1000,-1,-1,-1,'default',0
   from ${hive_db}.vehicle_basic_data_info_day where trip_mileage = 0 and trip_fuel > 0 and day = ${part_time}
union all
select tid,-1,-1,-1,5,-1,-1,-1,-1,-1,trip_mileage/1000,trip_mileage_total/1000 as daily_driving_mileage,cast(trip_mileage_total/trip_mileage as decimal(10,2)) as daily_driving_mileage_ratio,-1,-1,-1,-1,'default'
,0
  from ${hive_db}.vehicle_basic_data_info_day where trip_mileage_total < 0.8*trip_mileage and day = ${part_time}
EOF
#union all
#select trip.tid
#       ,trip.trip_id
#       ,trip.route_start_time
#       ,trip.route_end_time
#       ,3
#       ,trip.trip_mileage/1000 as trip_mileage
#       ,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1,-1
#       ,concat('{\"gpsTime\":\"',from_unixtime(cast(cast(nvl(locationpoint.gpsTime,0) as bigint)/1000 as bigint),'yyyy/MM/dd HH:mm:ss'),'\",\"speed":\"',locationpoint.speed/100,'\",\"rotation":\"',locationpoint.rotation/100,'\"}') as point_abnormal_info
#  from
#(select tid,tripid,gpsTime,speed,rotation from ${hive_db}.dwc_vehicle_track_protocol
# where speed > 60*100 and rotation < 800*100 and day = ${part_time}) locationpoint
#  join
#(select tid,trip_id,route_start_time,route_end_time,trip_mileage from ${hive_db}.trip_statistics
# where day = ${part_time}) trip
#    on locationpoint.tid = trip.tid
#   and locationpoint.tripid = trip.trip_id
)

#生成导出临时表
sql_suffix_tmp=$(cat <<EOF
insert overwrite table ${hive_db}.dws_vehicle_driving_abnormal_statistics_sd
select tid,trip_id,day,trip_start_time,trip_end_time,abnormal_type,trip_mileage,trip_fuel
        ,trip_fuel_100km,trip_break_mileage,trip_break_mileage_ratio,daily_mileage
        ,daily_driving_mileage,daily_driving_mileage_ratio,daily_fuel
        ,vehicle_agg_mileage,vehicle_agg_feul,vehicle_agg_feul_100km,point_abnormal_info
  from ${hive_db}.dws_vehicle_driving_abnormal_statistics_pdi
 where day = ${part_time}
   and export_type = 0
EOF
)

CUR_DIR=$(cd "$(dirname "$0")"; pwd)
hive_config=$(cat  ${CUR_DIR}/hive.config)
sql="${hive_config}${sql_suffix}"
echo "导入dws_vehicle_driving_abnormal_statistics的SQL为：${sql}"
tmpsql="${hive_config}${sql_suffix_tmp}"
echo "导入dws_vehicle_driving_abnormal_statistics_sd的SQL为：${tmpsql}"

#执行语句
#echo "导入dws_vehicle_driving_abnormal_statistics开始，开始时间:$(`date +%s`)"
hive -e "${sql}"
#echo "导入dws_vehicle_driving_abnormal_statistics_sd开始，开始时间:$(`date +%s`)"
hive -e "${tmpsql}"

if [[ $? -ne 0 ]];then
  echo "导入dws_vehicle_driving_abnormal_statistics失败，耗时$((`date +%s`-START_TIME))秒..."
else
  echo "导入dws_vehicle_driving_abnormal_statistics成功，耗时$((`date +%s`-START_TIME))秒..."
fi